import requests
import time
from lxml import etree
import json

headers={'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'}

url='https://jiaotong.baidu.com/trafficindex/city/list'
html = requests.get(url).text
data=json.loads(html)
datas=data['data']['list'] #data下list对应的值
#存储为json格式
with open('./jiaotong.json','w+') as fq:
    for d in datas:
        fq.write(json.dumps(d,ensure_ascii=False)+'\n')
fq.close()

#存储到csv
import csv
csvfile = open('./jiaotong.csv','w+',newline='',encoding='utf-8')
writer=csv.writer(csvfile)
filedNames=['time','citycode','cityletter','cityname','index','last_index','index_level','speed','city_coords','provincecode','provincename','weekRate']
writer=csv.DictWriter(csvfile,fieldnames=filedNames) #实例化一个字典格式的写入对象
writer.writeheader()
writer.writerows(datas) #将数据一次性写入
csvfile.close()
#存储到数据库
import pymysql
coon = pymysql.connect(host='localhost',user='root',password='33570638',db='pythondata',port=3306,charset='utf8')#创建数据库链接
cursor = coon.cursor()  #创建游标对象
#datas列表对象
for data in datas:
    time=data['time']
    citycode=data['citycode']
    cityname=data['cityname']
    index1=data['index']
    last_index=data['last_index']
    index_level=data['index_level']
    speed=data['speed']
    city_coords=data['city_coords']
    provincecode=data['provincecode']
    provincename=data['provincename']
    weekRate=data['weekRate']
    cursor.execute("insert into jiaotong(time,citycode,cityname,index1,last_index,"
                   "index_level,speed,city_coords,provincecode,provincename,weekRate)"
                   "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
                   (str(time),str(citycode),cityname,index1,last_index,str(index_level),speed,
                    city_coords,str(provincecode),provincename,str(weekRate)))
coon.commit()  #提交数据




























